﻿using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace KPIS.GERP.GHRMS.DAL
{
    public class PayrollDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        public DataTable GetReport(int year, int month, int emp_seq)
        {
            DataTable dtb;
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@month", month);
                dbManager.AddParameters(2, "@emp_seq", emp_seq);

//                strSQL = @"
//select count(*)
//from fim_payroll_sentpayment_combine comb
//inner join fim_payments p on comb.pay_seq = p.pay_seq
//where comb.record_status <> 'D'
//and p.record_status = 'A'
//and comb.year_no = @year
//and month_no = @month
//";//

//                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

//                if (dtb.Rows.Count > 0 && Convert.ToInt32(dtb.Rows[0][0]) == 0)
//                {
//                    return null;
//                }

                strSQL = @"
select t.*
, coalesce(t.Salary, 0) + coalesce(t.ExtraAdd, 0) + coalesce(t.Previous, 0) + coalesce(t.SurveyAdd, 0) as SumIncome
, coalesce(t.Tax, 0) + coalesce(t.Coop, 0) + coalesce(t.HouseBank, 0) + coalesce(t.OfficeLoan, 0) + coalesce(t.PFStaff, 0) + coalesce(t.OtherDeduction, 0) as SumDeduction
--, (coalesce(t.Salary, 0) + coalesce(t.ExtraAdd, 0) + coalesce(t.Previous, 0)) - (coalesce(t.Tax, 0) + coalesce(t.Coop, 0) + coalesce(t.HouseBank, 0) + coalesce(t.OfficeLoan, 0) + coalesce(t.PFStaff, 0) + coalesce(t.OtherDeduction, 0)) as Net
, coalesce(t2.TotalIncomeInYear, 0) as TotalIncomeInYear
, coalesce(t2.TotalTaxInYear, 0) as TotalTaxInYear
, coalesce(t2.TotalPFStaff, 0) as TotalPFStaff
, coalesce(t2.TotalPFOffice, 0) as TotalPFOffice
, '' as NetThaiText
, '' as BankAccountNo
from (
select concat(np.prefix_name, emp.first_name, ' ', emp.last_name) as EmpName
, emp.citizen as TaxPayerId
, j.job_site_name as JobsiteName
, prd.[year] as YearNo
, case prd.[month]
when 1 then 'มกราคม'
when 2 then 'กุมภาพันธ์'
when 3 then 'มีนาคม'
when 4 then 'เมษายน'
when 5 then 'พฤษภาคม'
when 6 then 'มิถุนายน'
when 7 then 'กรกฎาคม'
when 8 then 'สิงหาคม'
when 9 then 'กันยายน'
when 10 then 'ตุลาคม'
when 11 then 'พฤศจิกายน'
when 12 then 'ธันวาคม'
else '' end as MonthNo
, coalesce(prd.salary, 0) as Salary
, coalesce(prd.extra_add, 0) as ExtraAdd
, coalesce(prd.previous, 0) as Previous
, coalesce(prd.tax, 0) as Tax
, coalesce(prd.coop, 0) as Coop
, coalesce(prd.house_bank, 0) as HouseBank
, coalesce(prd.office_loan, 0) as OfficeLoan
, coalesce(prd.pf_staff, 0) as PFStaff
, coalesce(prd.other_deduction, 0) as OtherDeduction
, coalesce(prd.pf_office, 0) as PFOffice
, coalesce(prd.survey_add, 0) as SurveyAdd
, coalesce(prd.net, 0) as Net
from fim_payroll_dtls prd
left join hrm_ps_employees emp on prd.emp_seq = emp.emp_seq
left join mas_name_prefixes np on emp.name_prefix_seq = np.name_prefix_seq
left join hrm_mp_job_sites j on emp.working_job_site_seq = j.job_site_seq
where prd.emp_seq = @emp_seq
and prd.[year] = @year
and prd.[month] = @month
) t
left join (
select prd.[year]
, sum(coalesce(prd.salary, 0) + coalesce(prd.extra_add, 0) + coalesce(prd.previous, 0) + coalesce(prd.survey_add, 0)) as TotalIncomeInYear
, sum(coalesce(prd.tax, 0)) as TotalTaxInYear
, sum(coalesce(prd.pf_staff, 0)) as TotalPFStaff
, sum(coalesce(prd.pf_office, 0)) as TotalPFOffice
from fim_payroll_dtls prd

where prd.emp_seq = @emp_seq
and prd.[year] = @year
and prd.[month] <= @month
group by prd.[year]
) t2 on t.[YearNo] = t2.[year]
";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetBankAccountNo(int emp_seq)
        {
            DataTable dtb;
            string strSQL = "";

            try
            {
                strSQL = @"
select bank_acc_no
from hrm_ps_emp_book_banks
where emp_seq = @emp_seq
and record_status not like 'D'
";
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    return dtb.Rows[0]["bank_acc_no"].ToString();
                }

                return "";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
